import calendar
from datetime import datetime
import chinese_calendar as cc
from prettytable import PrettyTable
from openpyxl import Workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import PatternFill
from openpyxl.styles import Font, Alignment, Border, Side

def rgb_to_hex(r, g, b):
    return f"{r:02X}{g:02X}{b:02X}"

def get_month_details(year, month):
    num_days = calendar.monthrange(year, month)[1]
    weekdays_cn = ['一', '二', '三', '四', '五', '六', '日']
    workday_count = 0
    month_details = []
    
    for day in range(1, num_days + 1):
        date = datetime(year, month, day)
        weekday_cn = weekdays_cn[date.weekday()]
        if cc.is_holiday(date):
            workday_or_holiday = '放假'
        else:
            workday_or_holiday = '工作日'
            workday_count += 1
        
        month_details.append((day, weekday_cn, workday_or_holiday))
    
    return month_details, workday_count

# 输入年月
year = 2025
month = 4

# 获取月份详情
details, workday_count = get_month_details(year, month)

# 定义学生名单
#students = ["张三", "李四", "王五"]
#students = ["谢荣浩","天昊","天昱","绍睿","韦鋆城","邓博扬","罗嘉骏","梁奥睿","赵峻熙","卢俊宇","黄滢玥","思晴","何奕燊","吴芊伊","陈禹仲","谭祺霖","李承晔","赵辰语","芷安","梁吉然","梁喆然","易俊辰","肖浩然","宗宗","陈先睿","吴胤祺"]
students = ["谢荣浩","天昊","天昱","绍睿","韦鋆城","邓博扬","罗嘉骏","梁奥睿","赵峻熙","卢灵熙","黄滢玥","思晴","何奕燊","吴芊伊",
            "陈禹仲","谭祺霖","李承晔","黄宸希","赵辰语","芷安","梁吉然","梁喆然","易俊辰","肖浩然","宗宗","吴胤祺","陈瑞楠","张璟林"]

# 创建表格
table = PrettyTable()
table.field_names = ["日期", "星期", "托管类型"] + students

# 填充表格数据
data = []
for detail in details:
    if detail[2] == "工作日":
        # 午托行
        row_morning = [f"{year}-{month:02d}-{detail[0]:02d}", detail[1], "午托"] + [" " for _ in students]
        data.append(row_morning)
        table.add_row(row_morning)
        
        # 晚托行
        row_evening = [f"{year}-{month:02d}-{detail[0]:02d}", detail[1], "晚托"] + [" " for _ in students]
        data.append(row_evening)
        table.add_row(row_evening)
    else:
        # 放假行（也表示两行）
        row_holiday_morning = [f"{year}-{month:02d}-{detail[0]:02d}", detail[1], "--"] + ["--" for _ in students]
        data.append(row_holiday_morning)
        table.add_row(row_holiday_morning)
        row_holiday_evening = [f"{year}-{month:02d}-{detail[0]:02d}", detail[1], "--"] + ["--" for _ in students]
        data.append(row_holiday_evening)
        table.add_row(row_holiday_evening)


# 打印表格到控制台
print(table)
print(f"{year}年{month}月一共有 {workday_count} 个工作日")

# 输出到 .xlsx 文件
wb = Workbook()
ws = wb.active
ws.title = f"{year}年{month}月"

# 添加标题行
ws.append(["日期", "星期", "托管类型"] + students)

color_blue = rgb_to_hex(141, 180, 226)
color_purple = rgb_to_hex(112, 48, 160)
color_white = rgb_to_hex(255, 255, 255)
# 定义填充样式
fill_morning = PatternFill(start_color=color_blue, end_color=color_blue, fill_type="solid")  # 淡蓝色
fill_evening = PatternFill(start_color=color_white, end_color=color_white, fill_type="solid")  # 淡绿色
fill_holiday = PatternFill(start_color=color_purple, end_color=color_purple, fill_type="solid")  # 淡绿色
# 添加数据行并设置颜色
for row in data:
    ws.append(row)
    row_idx = ws.max_row
    if row[2] == "午托":
        for col_idx in range(3, len(row) + 1):
            ws.cell(row=row_idx, column=col_idx).fill = fill_morning
    elif row[2] == "晚托":
        for col_idx in range(3, len(row) + 1):
            ws.cell(row=row_idx, column=col_idx).fill = fill_evening
    elif row[2] == "--":
        for col_idx in range(3, len(row) + 1):
            ws.cell(row=row_idx, column=col_idx).fill = fill_holiday

# 合并相同的日期和星期列的单元格
row_idx = 2  # 数据开始的行（Excel中的行编号从1开始，首行为标题行）
#while row_idx <= len(data) + 1:
while row_idx <= 62:
    # 获取当前日期和星期值
    date_value = ws[f"A{row_idx}"].value
    weekday_value = ws[f"B{row_idx}"].value
    
    # 确定连续相同的日期和星期天数
    start_row = row_idx
    row_idx += 1
    cur_date_value = ws[f"A{row_idx}"].value
    while row_idx <= len(data) + 1 and cur_date_value == date_value:
        row_idx += 1
        cur_date_value = ws[f"A{row_idx}"].value
    
    # 如果有超过1行相同的日期，合并这些行的日期和星期列
    if row_idx - start_row > 1:
        ws.merge_cells(f"A{start_row}:A{row_idx - 1}")
        ws.merge_cells(f"B{start_row}:B{row_idx - 1}")

# 设置所有单元格的样式
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=len(students) + 3):
    for cell in row:
        # 设置字体大小为 14
        cell.font = Font(size=14)
        # 设置左右居中、上下居中
        cell.alignment = Alignment(horizontal='center', vertical='center')
        # 设置单元格边框
        border_style = Side(border_style="thin", color="000000")
        cell.border = Border(left=border_style, right=border_style, top=border_style, bottom=border_style)

# 设置列宽为自动宽度（包含标题行）
for col in ws.columns:
    max_length = 0
    column_letter = get_column_letter(col[0].column)  # 获取列字母
    for cell in col:
        if cell.value:  # 如果单元格有值
            try:
                # 将单元格内容转换为字符串计算长度
                max_length = max(max_length, len(str(cell.value)))
            except:
                pass
    # 设置列宽为最长内容的长度 + 2（增加一些额外宽度避免过于紧凑）
    ws.column_dimensions[column_letter].width = max_length + 6

# 保存 Excel 文件
# 获取当前时间
now = datetime.now()
# 格式化为 "23-15-46" 形式
time_str1 = now.strftime("%H-%M-%S")
print(time_str1)  # 输出：23-15-46
filename = f"{year}-{month:02d}-{time_str1}.xlsx"
wb.save(filename)
print(f"表格已保存到 {filename}")
